/********************************************************************************
Script name	: StarterKit.Constraint
Description	: Create constraint for database
********************************************************************************/

PRINT 'Creating constraints for dbo.LookupCode'
GO

ALTER TABLE dbo.[LookupCode] ADD 
	CONSTRAINT FK_LookupCode_LookupID FOREIGN KEY
	(
		LookupID
	) 
	REFERENCES dbo.[Lookup]
	(
		LookupID
	)
GO

PRINT 'Creating constraints for dbo.LookupValue'
GO

ALTER TABLE [dbo].[LookupValue]  ADD  
	CONSTRAINT [FK_LookupValue_LookupCode] FOREIGN KEY
	(
		[LookupCodeId]
	)
	REFERENCES [dbo].[LookupCode] 
	(
		[CodeId]
	)
GO
	

PRINT 'Creating constraints for dbo.MarketState'
GO

ALTER TABLE dbo.[MarketState] ADD 
	CONSTRAINT FK_MarketStateId_OfflinebyUserId FOREIGN KEY
	(
		OfflinebyUserId
	) 

	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO

ALTER TABLE dbo.[MarketState] ADD 
	CONSTRAINT FK_MarketStateId_OnlinebyUserId FOREIGN KEY
	(
		OnlinebyUserId
	) 

	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO

PRINT 'Creating constraints for dbo.UserInfo'
GO

ALTER TABLE dbo.[UserInfo] ADD 
	CONSTRAINT FK_UserInfo_AddressID FOREIGN KEY
	(
		AddressID
	) 
	REFERENCES dbo.[Address]
	(
		AddressID
	)
GO

ALTER TABLE dbo.[UserInfo] ADD 
	CONSTRAINT FK_UserInfo_ContactInformationId FOREIGN KEY
	(
		ContactInformationId
	) 
	REFERENCES dbo.[ContactInformation]
	(
		ContactInformationId
	)
GO

PRINT 'Creating constraints for dbo.Address'
GO

ALTER TABLE dbo.[Address] ADD 
	CONSTRAINT FK_Address_CountryId FOREIGN KEY
	(
		CountryId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


PRINT 'Creating constraints for dbo.MarketProperty'
GO

ALTER TABLE dbo.[MarketProperty] ADD
	CONSTRAINT FK_MarketProperty_MarketId FOREIGN KEY
	(
		MarketId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


ALTER TABLE dbo.[MarketProperty] ADD
	CONSTRAINT FK_MarketProperty_QuantityUnitId FOREIGN KEY
	(
		QuantityUnitId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


ALTER TABLE dbo.[MarketProperty] ADD
	CONSTRAINT FK_MarketProperty_PriceUnitId FOREIGN KEY
	(
		PriceUnitId
	) 
	REFERENCES dbo.[Currency]
	(
		CurrencyId
	)
GO

PRINT 'Creating constraints for dbo.SettlementContent'
GO

ALTER TABLE dbo.[SettlementContent] ADD 
	CONSTRAINT FK_SettlementContent_SettlementId FOREIGN KEY
	(
		[SettlementId]
	) 
	REFERENCES dbo.[Settlement]
	(
		[SettlementId]
	)
GO



ALTER TABLE dbo.[SettlementContent] ADD 
	CONSTRAINT FK_SettlementContent_MarketTypeId FOREIGN KEY
	(
		[MarketTypeId]
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

PRINT 'Creating constraints for dbo.Client'
GO


ALTER TABLE dbo.[Client] ADD 
	CONSTRAINT FK_Client_AddressId FOREIGN KEY
	(
		AddressId
	) 
	REFERENCES dbo.[Address]
	(
		AddressId
	)
GO


ALTER TABLE dbo.[Client] ADD 
	CONSTRAINT FK_Client_ClientTypeId FOREIGN KEY
	(
		ClientTypeId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Client] ADD 
	CONSTRAINT FK_Client_ContactInformationId FOREIGN KEY
(
		ContactInformationId
	) 
	REFERENCES dbo.[ContactInformation]
	(
		ContactInformationId
	)
GO

ALTER TABLE dbo.[Client] ADD 
	CONSTRAINT FK_Client_LimitMeasureId FOREIGN KEY
	(
		LimitMeasureId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

PRINT 'Creating constraints for dbo.Client2Preference'
GO


ALTER TABLE dbo.[Client2Preference] ADD 
	CONSTRAINT FK_Client2Preference_PreferenceId FOREIGN KEY
	(
		PreferenceId
	) 
	REFERENCES dbo.[Preference]
	(
		PreferenceId
	)
GO


ALTER TABLE dbo.[Client2Preference] ADD 
	CONSTRAINT FK_Client2User_ParentClientId FOREIGN KEY
	(
		ParentClientId
	) 
	REFERENCES dbo.[Client]
	(
		ClientId
	)
GO

ALTER TABLE dbo.[Client2Preference] ADD 
	CONSTRAINT FK_Client2User_ChildClientId FOREIGN KEY
	(
		ChildClientId
	) 
	REFERENCES dbo.[Client]
	(
		ClientId
	)
GO


PRINT 'Creating constraints for dbo.Client2User'
GO


ALTER TABLE dbo.[Client2User] ADD 
	CONSTRAINT FK_Client2User_UserInfoId FOREIGN KEY
	(
		UserInfoId
	) 
	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO


ALTER TABLE dbo.[Client2User] ADD 
	CONSTRAINT FK_Client2User_ClientId FOREIGN KEY
	(
		ClientId
	) 
	REFERENCES dbo.[Client]
	(
		ClientId
	)
GO


PRINT 'Creating constraints for dbo.Preference'
GO

ALTER TABLE dbo.[Preference] ADD 
	CONSTRAINT FK_Preference_ClearingId FOREIGN KEY
	(
		ClearingId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Preference] ADD 
	CONSTRAINT FK_Preference_TermsConditionsId FOREIGN KEY
	(
		TermsConditionsId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


ALTER TABLE dbo.[Preference] ADD
	CONSTRAINT FK_Preference_CurrencyId FOREIGN KEY
	(
		CurrencyId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Preference] ADD 
	CONSTRAINT FK_Preference_LawId FOREIGN KEY
	(
		LawId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Preference] ADD 
	CONSTRAINT FK_Preference_ArbitrationId FOREIGN KEY
	(
		ArbitrationId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


PRINT 'Creating constraints for dbo.Broker2Client'
GO

ALTER TABLE dbo.[Broker2Client] ADD 
	CONSTRAINT FK_Broker2Client_UserInfoId FOREIGN KEY
	(
		UserInfoId
	) 
	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO


ALTER TABLE dbo.[Broker2Client] ADD 
	CONSTRAINT FK_Broker2Client_ClientId FOREIGN KEY
	(
		ClientId
	) 
	REFERENCES dbo.[Client]
	(
		ClientId
	)
GO



PRINT 'Creating constraints for dbo.MarketEntity'
GO

ALTER TABLE dbo.[MarketEntity] ADD 
	CONSTRAINT FK_MarketEntity_IndexId FOREIGN KEY
	(
		IndexId
	) 
	REFERENCES dbo.[MarketIndex]
	(
		MarketIndexId
	)
GO

ALTER TABLE dbo.[MarketEntity] ADD 
	CONSTRAINT FK_MarketEntity_MarketId FOREIGN KEY
	(
		MarketId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[MarketEntity] ADD 
	CONSTRAINT FK_MarketEntity_MarketGroupId FOREIGN KEY
	(
		MarketGroupId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[MarketEntity] ADD 
	CONSTRAINT FK_MarketEntity_MarketTypeId FOREIGN KEY
	(
		MarketTypeId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[MarketEntity] ADD 
	CONSTRAINT FK_MarketEntity_TradePeriodBeginId FOREIGN KEY
	(
		TradePeriodBeginId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[MarketEntity] ADD 
	CONSTRAINT FK_MarketEntity_TradePeriodEndId FOREIGN KEY
	(
		TradePeriodEndId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

PRINT 'Creating constraints for dbo.MarketEntityLink'
GO

ALTER TABLE dbo.[MarketEntityLink] ADD 
	CONSTRAINT FK_MarketEntityLink_MarketId FOREIGN KEY
	(
		MarketId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


ALTER TABLE dbo.[MarketEntityLink] ADD 
	CONSTRAINT FK_MarketEntityLink_MarketGroupId FOREIGN KEY
	(
		MarketGroupId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[MarketEntityLink] ADD 
	CONSTRAINT FK_MarketEntityLink_MarketTypeId FOREIGN KEY
	(
		MarketTypeId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

PRINT 'Creating constraints for dbo.PEXIndex'
GO

ALTER TABLE dbo.[PEXIndex] ADD 
	CONSTRAINT FK_PEXIndex_MarketEntityId FOREIGN KEY
	(
		MarketEntityId
	) 
	REFERENCES dbo.[MarketEntity]
	(
		MarketEntityId
	)
GO

PRINT 'Creating constraints for dbo.MarketEntity2UserInfo'
GO

ALTER TABLE dbo.[MarketEntity2UserInfo] ADD 
	CONSTRAINT FK_MarketEntity2UserInfo_MarketEntityId FOREIGN KEY
	(
		MarketEntityId
	) 
	REFERENCES dbo.[MarketEntity]
	(
		MarketEntityId
	)
GO

ALTER TABLE dbo.[MarketEntity2UserInfo] ADD 
	CONSTRAINT FK_MarketEntity2UserInfo_UserInfoId FOREIGN KEY
	(
		UserInfoId
	) 
	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO

PRINT 'Creating constraints for dbo.Order'
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_MarketStateId FOREIGN KEY
	(
		[MarketStateId]
	) 
	REFERENCES dbo.[MarketState]
	(
		[MarketStateId]
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_MarketEntityId FOREIGN KEY
	(
		MarketEntityId
	) 
	REFERENCES dbo.[MarketEntity]
	(
		MarketEntityId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_ClientId FOREIGN KEY
	(
		ClientId
	) 
	REFERENCES dbo.[Client]
	(
		ClientId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_LocationId FOREIGN KEY
	(
		LocationId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_DeliveryId FOREIGN KEY
	(
		DeliveryId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_Incoterms2010Id FOREIGN KEY
	(
		Incoterms2010Id
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_DemurrageId FOREIGN KEY
	(
		DemurrageId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_InspectionSplitId FOREIGN KEY
	(
		InspectionSplitId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_TraderId FOREIGN KEY
	(
		TraderId
	) 

	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO

ALTER TABLE dbo.[Order] ADD 
	CONSTRAINT FK_Order_BrokerId FOREIGN KEY
	(
		BrokerId
	) 

	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO

PRINT 'Creating constraints for dbo.MarketIndexRate'
GO

ALTER TABLE dbo.[MarketIndexRate] ADD 
	CONSTRAINT FK_MarketIndexRate_MarketIndexId FOREIGN KEY
	(
		[MarketIndexId]
	) 
	REFERENCES dbo.[MarketIndex]
	(
		[MarketIndexId]
	)
GO


PRINT 'Creating constraints for dbo.TradeActivity'
GO

ALTER TABLE dbo.[TradeActivity] ADD 
	CONSTRAINT FK_TradeActivity_MarketStateId FOREIGN KEY
	(
		[MarketStateId]
	) 
	REFERENCES dbo.[MarketState]
	(
		[MarketStateId]
	)
GO

ALTER TABLE dbo.[TradeActivity] ADD 
	CONSTRAINT FK_TradeActivity_MarketEntityId FOREIGN KEY
	(
		[MarketEntityId]
	) 
	REFERENCES dbo.[MarketEntity]
	(
		[MarketEntityId]
	)
GO


ALTER TABLE dbo.[TradeActivity] ADD 
	CONSTRAINT FK_TradeActivity_SellerOrderId FOREIGN KEY
	(
		[SellerOrderId]
	) 
	REFERENCES dbo.[Order]
	(
		[OrderId]
	)
GO

ALTER TABLE dbo.[TradeActivity] ADD 
	CONSTRAINT FK_TradeActivity_BuyerOrderId FOREIGN KEY
	(
		[BuyerOrderId]
	) 
	REFERENCES dbo.[Order]
	(
		[OrderId]
	)
GO

ALTER TABLE dbo.[TradeActivity] ADD 
	CONSTRAINT FK_TradeActivity_LocationId FOREIGN KEY
	(
		LocationId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO

ALTER TABLE dbo.[TradeActivity] ADD 
	CONSTRAINT FK_TradeActivity_DeliveryId FOREIGN KEY
	(
		DeliveryId
	) 
	REFERENCES dbo.[LookupCode]
	(
		CodeId
	)
GO


PRINT 'Creating constraints for dbo.TradeActivityComment'
GO

ALTER TABLE dbo.[TradeActivityComment] ADD 
	CONSTRAINT FK_TradeActivityComment_UserInfoId FOREIGN KEY
	(
		UserInfoId
	) 

	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO

ALTER TABLE dbo.[TradeActivityComment] ADD 
	CONSTRAINT FK_TradeActivityComment_TradeActivityId FOREIGN KEY
	(
		TradeActivityId
	) 

	REFERENCES dbo.[TradeActivity]
	(
		TradeActivityId
	)
GO


PRINT 'Creating constraints for dbo.Notification'
GO

ALTER TABLE dbo.[Notification] ADD 
	CONSTRAINT FK_Notification_UserInfoId FOREIGN KEY
	(
		UserInfoId
	) 

	REFERENCES dbo.[UserInfo]
	(
		UserInfoId
	)
GO